@@identity set to NULL

[Diana Williams]

I have discovered a problem(?) with using replication on 7.0. It seems, no matter which type of replication I use (and I've used all of them), once the server is set up to be a publisher the global identity value is set to NULL. This doesn't seem to affect the identity values in existing tables but could very well affect the program code running the front end program.

I tried creating a new database on the same server just to see what would happen and the @@identity on that database is also NULL.

Does anyone know why this happens and is there a workaround to this? Again, it doesn't seem to affect the tables on which there are identity columns, they are incrementing properly, but I'm worried about the front end programs that are already in existence and are in the process of being developed.

[Jonathan Huang]

@@identity value is not decided UNLESS you have inserted a row to a table
with identity column. So if you have not inserted any thing to the table, it
is always null. Again, be very careful with the current @@identity value as
it is not global as you may think. @@identity value is only for the last
inserted row for the current connection. for example, if you inserted a row and
got @@identity=100, then another user inserted a row and his @@identity=101,
if you select @@identity, this value will still be 100 NOT 101. Even so,
a third user has just connected to SQL Server, he will get null value not 101
for @@identity.

In summary, do not use @@identity to do replication or track the last
inserted column to the table unless you are the only user dealing with
only one table.

------------
Diana Williams at 5/7/99 11:12:20 AM

I have discovered a problem(?) with using replication on 7.0. It seems,
no matter which type of replication I use (and I've used all of them),
once the server is set up to be a publisher the global identity value
is set to NULL. This doesn't seem to affect the identity values in
existing tables but could very well affect the program code running
the front end program.

I tried creating a new database on the same server just to see what
would happen and the @@identity on that database is also NULL.

Does anyone know why this happens and is there a workaround to this?
Again, it doesn't seem to affect the tables on which there are identity
columns, they are incrementing properly, but I'm worried ab